package org.mspring.mlog;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

/**
 *
 * @since 2014年4月16日
 * @author Gao Youbo
 */
public class SqlGender {

    public static void main(String[] args) throws SQLException {
        List<String> tables = getTables();
        List<String> sqls = new ArrayList<>();
        for (String table : tables) {
            List<String> columns = getColumns(table);
            StringBuilder sb = new StringBuilder();
            sb.append("insert into ").append(table).append(" (");
            sb.append(getInsertColumn(columns));
            sb.append(") ");

            sb.append("select ");
            sb.append(getSeleteColumn(table, columns));
            sb.append(" from ").append(getSelectTable(table));
            sb.append(";");

            sqls.add(sb.toString());
            System.out.println(sb.toString());
        }
    }

    private static String getInsertColumn(List<String> columns) {
        StringBuilder sb = new StringBuilder();
        for (String c : columns) {
            sb.append(c).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }

    private static String getSeleteColumn(String table, List<String> columns) {
        StringBuilder sb = new StringBuilder();
        for (String c : columns) {
            if (StringUtils.equalsIgnoreCase(c, "sort")) {
                c = "order";
            }
            if (StringUtils.equalsIgnoreCase(table, "t_tree_item") && StringUtils.equalsIgnoreCase(c, "url")) {
                c = "call";
            }
            if (StringUtils.equalsIgnoreCase(c, "foreign_key")) {
                c = "fid";
            }
            if (StringUtils.equalsIgnoreCase(c, "foreign_type")) {
                c = "from";
            }
            sb.append("_").append(c).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }

    private static String getSelectTable(String table) {
        return "mlog_".concat(StringUtils.substringAfter(table, "t_"));
    }

    private static List<String> getTables() throws SQLException {
        try (Connection con = BaseDao.getConnection()) {
            List<String> tables = new ArrayList<>();
            ResultSet rs = con.createStatement().executeQuery("show tables");
            while (rs.next()) {
                String name = rs.getString(1);
                if (StringUtils.startsWith(name, "t_")) {
                    tables.add(name);
                }
            }
            return tables;
        }
    }

    private static List<String> getColumns(String table) throws SQLException {
        try (Connection con = BaseDao.getConnection()) {
            List<String> columns = new ArrayList<>();
            ResultSet rs = con.createStatement().executeQuery("desc " + table);
            while (rs.next()) {
                String column = rs.getString("field");
                columns.add(column);
            }
            return columns;
        }
    }
}

class BaseDao {

    private static final String URL = "jdbc:mysql://127.0.0.1:3306/mlog_new?useUnicode=true&characterEncoding=utf8";

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, "root", "gaoyoubo");
    }

}
